package com.etc.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.rowset.CachedRowSet;

import com.etc.dao.IRecordsDao;
import com.etc.entity.Dept;
import com.etc.entity.Employees;
import com.etc.entity.Records;
import com.etc.entity.Statistics;
import com.etc.util.DBUtil;
import com.etc.util.PageData;

public class RecordsDaoImpl implements IRecordsDao {
	EmployeesDaoImpl empDao=new EmployeesDaoImpl();
	
	@Override
	public List<Records> showAllRecords() {
		CachedRowSet crs = DBUtil.executeQuery("select * from records");
		Employees emp = null;
		Records record = null;
		List<Records> list = new ArrayList<Records>();
		try {
			while (crs.next()) {

				int recordId = crs.getInt(1);
				// 对象在下面调dao方法生成
				int empId = crs.getInt(2);
				String beginTime = crs.getString(3);
				String endTime = crs.getString(4);
				int clockInDetails = crs.getInt(5);

				emp = empDao.queryById(empId);
				record = new Records(recordId, emp, beginTime, endTime, clockInDetails);
				list.add(record);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}
	
	/**
	 * 根据员工id查询对应的当天考勤记录
	 * id:员工id
	 * return:记录对象
	 */
	@Override
	public Records queryCurrentDayRecordById(int empId) {
		String sql = "select * from records where empId = ? and beginTime>= CURDATE() and beginTime < DATE_SUB(CURDATE(),INTERVAL -1 DAY)";
		CachedRowSet crs = DBUtil.executeQuery(sql,empId);
		Employees emp=null;
		Records records=null;
		try {
			if(crs.next()) {
				int recordId = crs.getInt(1);
				String beginTime = crs.getString(3);
				String endTime = crs.getString(4);
				int clockInDetails = crs.getInt(5);
				
				emp = empDao.queryById(empId);
				records=new Records(recordId, emp, beginTime, endTime, clockInDetails);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return records;
	}
	
	/**
	 * 根据员工id查询对应的本月所有记录
	 * id:员工id
	 * return: 记录集合
	 */
	@Override
	public List<Records> queryCurrentMonthRcordsById(int empId) {
		String sql = "SELECT * FROM records WHERE empId = ? and DATE_FORMAT(beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') order by beginTime desc";
		CachedRowSet crs = DBUtil.executeQuery(sql,empId);
		List<Records> list=new ArrayList<Records>();
		Employees emp=null;
		Records records=null;
		try {
			while(crs.next()) {
				int recordId = crs.getInt(1);
				String beginTime = crs.getString(3);
				String endTime = crs.getString(4);
				int clockInDetails = crs.getInt(5);
				emp = empDao.queryById(empId);
				records=new Records(recordId, emp, beginTime, endTime, clockInDetails);
				list.add(records);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 添加考勤记录
	 * records:记录对象
	 * return true/false
	 */
	@Override
	public boolean addRecord(int empId,int clockInDetails) {
		return DBUtil.executeUpdate("insert into records values(null,?,now(),null,?)", empId,clockInDetails);
	}
	
	/**
	 * 修改考勤记录
	 * record:记录对象
	 * return true/false
	 */
	@Override
	public boolean updateRecord(Records record) {
		return DBUtil.executeUpdate("update records set endTime = now(),clockInDetails = ? where empId = ? and beginTime>= CURDATE() and beginTime < DATE_SUB(CURDATE(),INTERVAL -1 DAY)",record.getClockInDetails(),record.getEmp().getEmpId());
	}
	/**
	 * 添加考勤记录(补卡)
	 * records:记录对象
	 * return true/false
	 */
	@Override
	public boolean addRecords(Records record ,int empId) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("insert into records values(null,?,?,?,?)", empId,record.getBeginTime(),record.getEndTime(),record.getClockInDetails());
	}
	/**
	 * 通过id和时间查员工考勤记录详情信息
	 * records:记录对象
	 * return List/集合
	 */
	@Override
	public List<Records> queryById(int empId,String beginTime,String endTime,int clockIn) {
		Records records= null;
		CachedRowSet rs=null;
		List<Records> list =new  ArrayList<Records>();
		if(clockIn==88) {
			rs=DBUtil.executeQuery("select * from records where clockInDetails>0 AND empId like ? AND beginTime>=? AND beginTime<=?",empId,beginTime+" 00:00:00",endTime+" 23:59:59");
		}
		if(clockIn!=88) {
			rs=DBUtil.executeQuery("select * from records where clockInDetails>0 AND empId like ? AND clockInDetails like ? AND beginTime>=? AND beginTime<=?",empId,clockIn,beginTime+" 00:00:00",endTime+" 23:59:59");
		}		
		try {
			while(rs.next()){
				Integer recordId = rs.getInt("recordId");
				String beginTime1 = rs.getString("beginTime");
				String endTime1 = rs.getString("endTime");			
				Integer clockInDetails = rs.getInt("clockInDetails");
				records=new Records(recordId,null,beginTime1, endTime1, clockInDetails);
				list.add(records);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * dao层
	 * 查询所有员工考勤记录简略信息
	 * Statistics:记录对象
	 * return List/集合
	 */
	@Override
	public List<Statistics> queryAllRecords(String begintime,
			String endtime,int empId,int clockIn,int deptId,String empName) {
		
		Statistics statistics= null;
		CachedRowSet rs=null;
		List<Statistics> list =new  ArrayList<Statistics>();
		//查询当月数据
		if (begintime=="" && endtime=="" && empId==0 && deptId==0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select  empId,clockInDetails,count(clockInDetails) from records  where DATE_FORMAT(beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') group by empId , clockInDetails");
		}
		//根据ID查询当月数据
		if (begintime=="" && endtime=="" && empId!=0 && deptId==0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select  A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where DATE_FORMAT(A.beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') AND A.empId=? group by A.empId,A.clockInDetails",empId);
		}
		//根据ID和时间查询当月数据
		if (begintime!="" && endtime!="" && empId!=0 && deptId==0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select  A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where A.beginTime BETWEEN ? AND ? AND A.empId=? group by A.empId,A.clockInDetails",begintime+" 00:00",endtime+" 23:59:59",empId);
		}
		//输入开始和结束时间，查询时间内的数据
		if (begintime!="" && endtime!="" && empId==0 && deptId==0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select  empId,clockInDetails,count(clockInDetails) from records  where beginTime BETWEEN ? AND ? group by empId , clockInDetails",begintime+" 00:00",endtime+" 23:59:59");
		}
		//输入开始时间  查询时间以后的数据
		if (begintime!="" && endtime=="" && empId==0 && deptId==0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select  empId,clockInDetails,count(clockInDetails) from records  where beginTime>=?  group by empId , clockInDetails",begintime+" 00:00");
		}
		
		//根据部门查询当月数据
		if (begintime=="" && endtime=="" && empId==0 && deptId!=0 && clockIn==88 && empName=="") {			
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where DATE_FORMAT(A.beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') AND B.deptId=? group by A.empId,A.clockInDetails",deptId);
		}
		//根据部门和时间查询当月数据
		if (begintime!="" && endtime!="" && empId==0 && deptId!=0 && clockIn==88 && empName=="") {
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where A.beginTime BETWEEN ? AND ? AND B.deptId=? group by A.empId,A.clockInDetails",begintime+" 00:00",endtime+" 23:59:59",deptId);
		}
		//根据姓名查询当月数据
		if (begintime=="" && endtime=="" && empId==0 && deptId==0 && clockIn==88 && empName!="") {			
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where DATE_FORMAT(A.beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') AND B.empName like ? group by A.empId,A.clockInDetails","%"+empName+"%");
		}
		//根据姓名和时间查询当月数据
		if (begintime!="" && endtime!="" && empId==0 && deptId==0 && clockIn==88 && empName!="") {
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where A.beginTime BETWEEN ? AND ? AND B.empName like ? group by A.empId,A.clockInDetails",begintime+" 00:00",endtime+" 23:59:59","%"+empName+"%");
		}
		//根据状态查询当月数据
		if (begintime=="" && endtime=="" && empId==0 && deptId==0 && clockIn!=88 && empName=="") {			
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where DATE_FORMAT(A.beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') AND A.clockInDetails like ? group by A.empId,A.clockInDetails",clockIn);
		}
		//根据状态和时间查询当月数据
		if (begintime!="" && endtime!="" && empId==0 && deptId==0 && clockIn!=88 && empName=="") {
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where A.beginTime BETWEEN ? AND ? AND A.clockInDetails like ? group by A.empId,A.clockInDetails",begintime+" 00:00",endtime+" 23:59:59",clockIn);
		}	
		
		//根据部门状态查询当月数据
		if (begintime=="" && endtime=="" && empId==0 && deptId!=0 && clockIn!=88 && empName=="") {			
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where DATE_FORMAT(A.beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') AND A.clockInDetails like ? AND B.deptId=? group by A.empId,A.clockInDetails",clockIn,deptId);
		}
		//根据部门状态和时间查询当月数据
		if (begintime!="" && endtime!="" && empId==0 && deptId!=0 && clockIn!=88 && empName=="") {
			rs = DBUtil.executeQuery("select A.empId,A.clockInDetails,count(clockInDetails) from records A inner join employees B on A.empId=B.empId where A.beginTime BETWEEN ? AND ? AND A.clockInDetails like ? AND B.deptId=? group by A.empId,A.clockInDetails",begintime+" 00:00",endtime+" 23:59:59",clockIn,deptId);
		}	
		try {
			while(rs.next()) {
				Integer staId = rs.getInt("empId");
				Integer clockInDetails = rs.getInt("clockInDetails");
				Integer normalDays = 0;
				Integer lateDays = 0;
				Integer earlyDepdays = 0;
				Integer leaveDays = 0;
				if(clockInDetails==0) {
					normalDays = rs.getInt("count(clockInDetails)");
				}
				if(clockInDetails==1) {
					lateDays = rs.getInt("count(clockInDetails)");
				}
				if(clockInDetails==2) {
					earlyDepdays = rs.getInt("count(clockInDetails)");
				}
				if(clockInDetails==3) {
					leaveDays = rs.getInt("count(clockInDetails)");
				}														
				statistics = new Statistics(staId, normalDays, lateDays, earlyDepdays, leaveDays,null);				
				list.add(statistics);							
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 通过id删除考勤记录
	 * records:记录对象
	 * return true/false
	 */
	@Override
	public boolean delRecords(int recordId) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("delete from records where recordId=?;",recordId);
	}
	/**
	 * 通过id修改考勤记录
	 * records:记录对象
	 * return true/false
	 */
	@Override
	public boolean updateRecords(Records record) {
		// TODO Auto-generated method stub
		return DBUtil.executeUpdate("update records set beginTime=?,endTime =?,clockInDetails=? where recordId=?;",record.getBeginTime(),record.getEndTime(),record.getClockInDetails(),record.getRecordId());
	}
	
	@Override
	public PageData showAllRecordsByPage(int pageNo, int pageSize) {
		// TODO Auto-generated method stub
		String sql = "select * from records  where beginTime>= CURDATE() and beginTime < DATE_SUB(CURDATE(),INTERVAL -1 DAY) LIMIT ?,?";
		CachedRowSet crs = DBUtil.executeQuery(sql, (pageNo - 1) * pageSize, pageSize);
		Employees emp = null;
		Records record = null;
		List<Records> list = new ArrayList<Records>();
		try {
			while (crs.next()) {
				int recordId = crs.getInt(1);
				int empId = crs.getInt(2);
				String beginTime = crs.getString(3);
				String endTime = crs.getString(4);
				int clockInDetails = crs.getInt(5);
				// 根据empId得到对象
				emp = empDao.queryById(empId);
				record = new Records(recordId, emp, beginTime, endTime, clockInDetails);
				list.add(record);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 2.获取分页的总条数
		int totalCount = 0;
		String sql2 = "select count(*) from records  where beginTime>= CURDATE() and beginTime < DATE_SUB(CURDATE(),INTERVAL -1 DAY)";
		CachedRowSet crs2 = DBUtil.executeQuery(sql2);
		try {
			while (crs2.next()) {
				totalCount = crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		PageData pageData = new PageData(list, pageNo, pageSize, totalCount);
		return pageData;
	}

	@Override
	public int countAllFullAttend(int clockInDetails) {
		// TODO Auto-generated method stub
		String sql = "SELECT count(*) FROM records WHERE clockInDetails = ? and DATE_FORMAT(beginTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')";
		CachedRowSet crs = DBUtil.executeQuery(sql,clockInDetails);
		int i=0;
		try {
			while (crs.next()) {
				i = crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return i;
	}


}
